CREATE TABLE `projects`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(300) NOT NULL,
	`description` TEXT NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `components`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`project_id` INT(11) NOT NULL,
	`name` VARCHAR(300) NOT NULL,
	`description` TEXT NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `versions`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`component_id` INT(11) NOT NULL,
	`name` VARCHAR(300) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `users`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(300) NOT NULL,
	`password` VARCHAR(200) NOT NULL,
	`userlevel_id` INT(11) NOT NULL,
	`email` VARCHAR(300) NOT NULL,
	`name`	TEXT NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `userlevel`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(300) NOT NULL,
	`color` VARCHAR(6) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;


CREATE TABLE `serverities`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(300) NOT NULL,
	`description` TEXT NOT NULL,
	`color` VARCHAR(6) NOT NULL
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `platforms`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(300) NOT NULL,
	`description` TEXT NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `oses`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`platform_id` INT(11) NOT NULL,
	`name` VARCHAR(300) NOT NULL, 
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `priorities`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(300) INT(11) NOT NULL,
	`description` TEXT NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `milestones`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`version_id` INT(11) NOT NULL,
	`name`	VARCHAR(300) NOT NULL,
	`assigned` INT(11) NOT NULL,
	`qa` INT(11) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `payments`(
	`id` INT(11) NOT NULL,
	`name` VARCHAR(300) NOT NULL,
	`description` TEXT NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `attachments`(
	`id` INT(11) NOT NULL,
	`filepath` VARCHAR(300) NOT NULL,
	`description` VARCHAR(300) NOT NULL,
	`ContentType` VARCHAR(300) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `dependancies`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`bug` INT(11) NOT NULL,
	`dependant` INT(11) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `states`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(300) NOT NULL,
	`description` TEXT NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `stateLinks`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`state_id` INT(11) NOT NULL,
	`previous_state` INT(11) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `bugs`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`project_id` INT(11) NOT NULL,
	`component_id` INT(11) NOT NULL,
	`reporter` INT(11) NOT NULL,
	`serverity_id` INT(11) NOT NULL, 
	`priority_id` INT(11) NOT NULL,
	`platform_id` INT(11) NOT NULL,
	`os_id` INT(11) NOT NULL,
	`hours` INT(11) NOT NULL,
	`deadline` DATETIME(),
	`sate_id` INT(11) NOT NULL,
	`url` VARCHAR(300) NOT NULL,
	`payment_id` INT(11) NOT NULL,
	`payment_details` VARCHAR(300) NOT NULL,
	`summary` VARCHAR(300) NOT NULL,
	`description` TEXT NOT NULL,
	`keywords` VARCHAR(300) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE = INNODB;

CREATE TABLE `work`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`bug_id` INT(11) NOT NULL,
	`user_id` INT(11) NOT NULL,
	`hours` VARHCAR(16) NOT NULL,
	`done` TEXT NOT NULL,
	`todo` TEXT NOT NULL,
	`additional` TEXT NOT NULL,
	`added` DATETIME(),
	PRIMARY KEY(`id`)
)ENGINE = MYISAM;

CREATE TABLE `comments`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`bug_id` INT(11) NOT NULL,
	`user_id` INT(11) NOT NULL,
	`comment` TEXT NOT NULL,
	`added` DATETIME(),
	PRIMARY KEY(`id`)
)ENGINE = MYISAM;